
/***********************************************************************/
USE [master]
GO

CREATE DATABASE LoanProcessingSystem
GO

USE LoanProcessingSystem
GO

CREATE TABLE UserType
(
	TypeID			INT PRIMARY KEY IDENTITY(1,1),
	TypeName		VARCHAR(50)
)
GO

CREATE TABLE [Branch]
(
	BranchID		INT PRIMARY KEY IDENTITY(1,1),
	BranchName		VARCHAR(100),
	[Address]		VARCHAR(100),
	PhoneNumber		VARCHAR(15),
	[Description]	VARCHAR(100),
	[Status]		BIT
)
GO

CREATE TABLE [User]
(
	UserID			INT PRIMARY KEY IDENTITY(1,1),
	Username		VARCHAR(50) UNIQUE,
	[Password]		VARCHAR(40)DEFAULT '123456',
	FullName		VARCHAR(50),
	[Address]		VARCHAR(100),
	BirthDay		SMALLDATETIME,
	Email			VARCHAR(100),
	[Identity]		CHAR(9) UNIQUE,
	Phone			VARCHAR(12),
	Sex				BIT,
	TypeID			INT REFERENCES UserType(TypeID),
	BranchID		INT REFERENCES Branch(BranchID),
	[IsLock]		BIT DEFAULT 0,
	[Status]		BIT DEFAULT 0,
	Note			VARCHAR(500),
)

GO

CREATE TABLE LoanType
(
	LoanTypeID		INT PRIMARY KEY IDENTITY(1,1),
	TypeOfLoan		VARCHAR(50),
	InterestRate	FLOAT,
	[Status]		BIT	/*
						 * Value = 0 Disable
						 * Value = 1 Enable
						 */
)
GO

CREATE TABLE FineLoan
(
	FineID			INT PRIMARY KEY IDENTITY(1,1),
	FromMoney		MONEY,
	ToMoney			MONEY,
	Fine			FLOAT
)
GO

CREATE TABLE Customer 
(
	CustomerID		INT PRIMARY KEY IDENTITY(1,1),
	UserID			INT REFERENCES [User](UserID),
	FirstName		VARCHAR(50),
	LastName		VARCHAR(50),
	[Address]		VARCHAR(100),
	BrithDay		SMALLDATETIME,
	PhoneNumber		VARCHAR(15),
	Email			VARCHAR(100),
	[Identity]		CHAR(9) UNIQUE,
	Sex				BIT,
	Organzination	VARCHAR(100),
	GrossSalary		MONEY,
	Note			VARCHAR(500),
	[Status]		BIT 
)
GO

CREATE TABLE LoanContact  
(
	LoanID			INT PRIMARY KEY IDENTITY(1,1),
	CustomerID		INT REFERENCES Customer(CustomerID),
	LoanTypeID		INT REFERENCES LoanType(LoanTypeID),
	UserID			INT REFERENCES [User](UserID),
	UserAccecpted	INT ,
	Amount			MONEY,
	Period			INT,
	DateStart		DATETIME,
	DateApproved	DATETIME, 
	IsAccepted		INT 
)
GO

CREATE TABLE Payment  
(
	PaymentID		INT PRIMARY KEY IDENTITY(1,1),
	LoanID			INT REFERENCES LoanContact(LoanID),
	[Month]			INT,
	Payment			MONEY,
	Interest		FLOAT,
	FineAmount		MONEY,
	TotalPay		MONEY,
	DureDate		DATETIME,
	DatePaid		DATETIME,
	[Status]		BIT
)
GO

/*********************************************************/
/* Insert into table */

-- User type Table 
INSERT INTO UserType VALUES('Administrator')
INSERT INTO UserType VALUES('Branch Manager')
INSERT INTO UserType VALUES('Emloyee')
GO
-- Branch Table 
INSERT INTO Branch VALUES ('QuicK Loan 1','12 Hue street','04389652638','',1)
INSERT INTO Branch VALUES ('QuicK Loan 2','18 Dien Bien Phu street','043568945272','',1)
INSERT INTO Branch VALUES ('QuicK Loan 3','8 Hang Bai street','04369852147','',1)
GO
-- Loan type type Table 
INSERT INTO LoanType VALUES('Home Loan',10,1)
INSERT INTO LoanType VALUES('Vehicle loan',13,1)
INSERT INTO LoanType VALUES('Personal loan',18,1)
INSERT INTO LoanType VALUES('Educational',5,1)
GO
-- Fine Table 
INSERT INTO FineLoan VALUES(1,5000,2)
INSERT INTO FineLoan VALUES(5001,20000,5)
INSERT INTO FineLoan VALUES(20001,0,8) -- 0 is max value  
GO
-- User Table 
-------------------------

INSERT INTO [User] VALUES('Senior','789123','Vu Manh Hung','119 Tam Trinh - Ha noi','1/1/1989','Hungvm_c00359@fpt.aptech.ac.vn','100979291','0985626595',1,1,1,0,0,'')
INSERT INTO [User] VALUES('employee','123456','Employee','HN','02/02/1990','employee@gmail.com','123456789','+84973456789',1,3,1,0,0,'') 
INSERT INTO [User] VALUES('Branch1','123456','Branch1','HN','05/06/1981','branch01@gmail.com','987654321','+84973456124',1,2,1,0,0,'') 

GO
-- Customer Table
INSERT INTO Customer VALUES (  2,'Bui Thanh','Long','Ha Noi - Viet Nam','07/02/1990','+84 973582899','longbt@fpt.aptech.ac.vn','121556743',1,'HA company',5000,'',0)
INSERT INTO Customer VALUES (  3,'Dinh Xuan','Dung','Nam Dinh - Viet Nam','08/07/1990','+84 12345678','dungdx@fpt.aptech.ac.vn','123456789',1,'FPT Arena',7000,'',0) 
INSERT INTO Customer VALUES (  3,'Nguyen Van','Sang','Ha Long - Viet Nam','12/09/1980','+84 9091562384','sang@gmail.com','457891234',0,'Viettel',9000,'',0) 
INSERT INTO Customer VALUES (  2,'Nguyen Thi ','Ngoc Mai','Quang Ninh - Viet Nam','09/16/1989','+84 98765412','ngocmai@yahoo.com.vn','102032548',0,'HanoiAmsecdam',10000,'',0) 
INSERT INTO Customer VALUES (  3,'Vu Van','Long','Ho Chi Minh City - Vietnam','07/02/1990','+84 9563256894','hcm@gmail.com','123789456',0,'HA company',5000,'',0) 

GO
--Loan contact
INSERT INTO LoanContact VALUES (
									1,
									1,
									2,
									0,
									100000,
									10,
									'2011-01-10',
									null,
									2
								)	
GO			
/**********************************************/		
/* ============Loan Type============*/

	-- view loan type all
	CREATE PROC View_All_LoanType
	AS
		SELECT * FROM LoanType
	GO

	-- view loan type enable
	CREATE PROC View_all_LoanType_Enable
	AS
		SELECT * FROM LoanType WHERE [Status] = 1
	GO
	
	--view loan type by id 
	CREATE PROC View_LoanType_By_ID
		@id INT 
	AS
		SELECT * FROM LoanType WHERE LoanTypeID = @id
	GO

	--view loan type by name 
	CREATE PROC View_LoanType_By_Name
		@name VARCHAR(50)
	AS
		SELECT * FROM LoanType WHERE TypeOfLoan LIKE ('%'+@name+'%')
	GO

	--Insert to loan type
	CREATE PROC Insert_LoanType
		@TypeOfLoan VARCHAR(50),
		@InterestRate FLOAT
	AS
		INSERT INTO LoanType VALUES (@TypeOfLoan,@InterestRate,1)
	GO

	--disable , enable loan type
	CREATE PROC Disable_LoanType
		@id INT
	AS
		UPDATE LoanType SET [Status] = 0 WHERE LoanTypeID = @id   
	GO

	CREATE PROC Enable_LoanType
		@id INT
	AS
		UPDATE LoanType SET [Status] = 1 WHERE LoanTypeID = @id   
	GO

	--Update to loan type
	CREATE PROC Update_LoanType
		@id INT,
		@TypeOfLoan VARCHAR(50),
		@InterestRate FLOAT		
	AS
		UPDATE LoanType SET TypeOfLoan = @TypeOfLoan, InterestRate = @InterestRate WHERE LoanTypeID = @id   
	GO

 
/* ============Fine Loan===========*/

	-- view fine loan all
	CREATE PROC View_all_FineLoan
	AS
		SELECT * FROM FineLoan
	GO

	--view fine loan by id 
	CREATE PROC View_FineLoan_By_ID
		@id INT 
	AS
		SELECT * FROM FineLoan WHERE FineID = @id
	GO

	--Insert to fine loan
	CREATE PROC Insert_FineLoan
		@To MONEY,
		@From MONEY,
		@Fine FLOAT
	AS
		INSERT INTO FineLoan VALUES (@To,@From,@Fine)
	GO

	--Delete to fine loan
	CREATE PROC Delete_FineLoan
		@id INT
	AS
		DELETE FROM FineLoan WHERE FineID = @id   
	GO

	--Update to fine loan
	CREATE PROC Update_FineLoan
		@id INT,
		@To MONEY,
		@From MONEY,
		@Fine FLOAT		
	AS
		UPDATE FineLoan SET ToMoney = @To, FromMoney = @From, Fine = @Fine WHERE FineID = @id   
	GO

 
/* ============ Branch ===========*/

	-- view Branch
	CREATE PROC View_all_Branch
	AS
		SELECT * FROM Branch

	GO
	
	CREATE PROC View_Branch_Active
	AS
		SELECT * FROM Branch WHERE status = 1
	GO

	--view Branch by id 
	CREATE PROC View_Branch_By_ID
		@id INT 
	AS
		SELECT * FROM Branch WHERE BranchID = @id
	GO

	CREATE PROC View_Branch_By_Name
		@name VARCHAR(50)
	AS
		SELECT * FROM Branch WHERE BranchName  LIKE ('%'+@name+'%')
	GO
	--Insert to Branch
	CREATE PROC Insert_Branch
		@name		 VARCHAR(100),
		@Address	 VARCHAR(100),
		@PhoneNumber VARCHAR(15),
		@Description VARCHAR(100),
		@Status		 BIT
	AS
		INSERT INTO Branch 
		VALUES(@name,@Address,@PhoneNumber,@Description,@Status)
	GO

	--Delete to Branch
	CREATE PROC Disable_Branch
		@id INT
	AS
		UPDATE Branch 
		SET Status = 0
		WHERE BranchID = @id   
	GO
	
	CREATE PROC Enable_Branch
		@id INT
	AS
		UPDATE Branch 
		SET Status = 1
		WHERE BranchID = @id   
	GO
	--Update to fine loan
	CREATE PROC Update_Branch
		@id INT,
		@name		 VARCHAR(100),
		@Address	 VARCHAR(100),
		@PhoneNumber VARCHAR(15),
		@Description VARCHAR(100),
		@Status		 BIT		
	AS
		UPDATE Branch 
		SET	BranchName = @name,
			Address = @Address, 
			PhoneNumber = @PhoneNumber,
			Description = @Description,
			Status	= @Status 
		WHERE BranchID = @id   
	GO

/*========== USER ===========*/

GO

CREATE PROC sp_CheckLogin
@user nvarchar(30),
@pass nvarchar(30)
AS
SELECT U.*, UT.*,B.*
FROM UserType UT INNER JOIN [USER] U ON UT.typeID = U.TypeID
INNER JOIN Branch B ON U.BranchID = B.BranchID
WHERE		
U.UserName = @user And 
U.passWord = @pass And 
U.isLock = 0 And U.[status] = 0

GO

-- PROC UPDATE PASSWORD
CREATE PROC sp_ChangePass
@UserName nvarchar(50),
@OldPassword nvarchar(50),
@NewPassword nvarchar(50)
AS
UPDATE [User]
SET Password = @NewPassword
WHERE userName = @UserName and 
password = @OldPassword

GO
-- PROC GET ALL USER
CREATE PROC sp_getUserAll
AS
Select U.*,typeName,BranchName
From [User] U INNER JOIN Branch B
on U.BranchId = B.BranchId INNER JOIN UserType UT on U.TypeId = UT.TypeId 
WHERE U.status = 0

GO

--PROC INSERT NEW USER
CREATE PROC sp_InsertUser
@userName nvarchar(30), 
@fullName nvarchar(100),
@address nvarchar(200),
@birthDay smalldatetime,
@email nvarchar(100),
@identity nchar(12),
@phone nchar(12),
@sex bit,
@typeUser int,
@branchId int,
@note nvarchar(500)
AS
Insert into [User](UserName,Password, FullName, Address, BirthDay, Email, [Identity], phone, sex, typeId, branchId, note, isLock, status)
values(@userName,'123456',@fullName,@address,@birthDay,@Email,@identity,@phone,@sex,@typeUser,@branchId,@note,0,0)

GO
-- PROC UPDATE USER
CREATE PROC sp_updateUser
@Id int,
@userName nvarchar(30),
@fullName nvarchar(100),
@address nvarchar(200),
@birthDay datetime,
@email nvarchar(100),
@identity nchar(12),
@phone nchar(12),
@sex bit,
@typeId int,
@branchId int,
@note nvarchar(500)
AS
UPDATE [USER]
SET
UserName = @userName,
fullName = @fullName,
Address = @Address,
birthDay = @birthDay,
Email = @Email,
[identity] =@identity,
phone =@phone,
sex = @sex,
typeId = @typeId,
branchId = @branchId,
note = @note
WHERE UserID = @Id

GO
--Delete to User
CREATE PROC sp_DeleteUser
@id INT
AS
UPDATE [USER] 
SET	status = 1
WHERE UserID = @id

GO 

--CHECK USER
CREATE PROC sp_CheckUser
@userName nvarchar(50)
AS
SELECT username 
from [User] 
where username = @userName and status = 0

GO

CREATE PROC sp_CheckIdentity
@identity nvarchar(10)
AS
SELECT [identity]
from [User] 
where [identity] = @identity and status = 0

GO
--LOCK USER
CREATE PROC sp_LockUser
@id INT
AS
UPDATE [User] 
SET isLock = 1
WHERE UserId =@id

GO
--UNLOCK USER
CREATE PROC sp_UnLockUser
@id INT
AS
UPDATE [User] 
SET isLock = 0
WHERE UserId =@id
GO 

--View User by id 
	CREATE PROC sp_getUserByID
		@id int 
	AS
		Select U.*,TypeName,BranchName , PhoneNumber, B.[Address] as BAddress , B.[Description] , B.Status as BStatus
		From [User] U INNER JOIN Branch B
		on U.BranchId = B.BranchId INNER JOIN UserType UT on U.TypeId = UT.TypeId 
		where U.UserID = @id
	GO

--SEARCH By Usernam
CREATE PROC sp_getUserByUserName
		@usename varchar(50) 
	AS
		Select U.*,TypeName,BranchName , PhoneNumber, B.[Address] as BAddress , B.[Description] , B.Status as BStatus
		From [User] U INNER JOIN Branch B
		on U.BranchId = B.BranchId INNER JOIN UserType UT on U.TypeId = UT.TypeId 
		where U.UserName like ('%' + @usename + '%')
	GO

--SEARCH BY FULL NAME
CREATE PROC sp_getUserByFullName
		@fullname varchar(50) 
	AS
		Select U.*,TypeName,BranchName , PhoneNumber, B.[Address] as BAddress , B.[Description] , B.Status as BStatus
		From [User] U INNER JOIN Branch B
		on U.BranchId = B.BranchId INNER JOIN UserType UT on U.TypeId = UT.TypeId 
		where U.fullName like '%' + @fullname + '%'
	GO

-- SEARCH BY IDENTITY
CREATE PROC sp_getUserByIdentity
@identity varchar(9)
AS
	Select U.*,TypeName,BranchName , PhoneNumber, B.[Address] as BAddress , B.[Description] , B.Status as BStatus
	From [User] U INNER JOIN Branch B
	on U.BranchId = B.BranchId INNER JOIN UserType UT on U.TypeId = UT.TypeId 
	where U.[identity] like @identity
GO

CREATE PROC sp_getUserByLock
@isLock int
AS
Select U.*,TypeName,BranchName , PhoneNumber, B.[Address] as BAddress , B.[Description] , B.Status as BStatus
From [User] U INNER JOIN Branch B
on U.BranchId = B.BranchId INNER JOIN UserType UT on U.TypeId = UT.TypeId 
where U.isLock =@isLock
/*======================USERTYPE=======================*/

GO
CREATE PROC sp_getUserType
AS
SELECT * FROM UserType

GO
/*======================LoanContact====================*/
	-- view all Loan contact
	
	

	CREATE PROC View_all_LoanContact
	AS
		SELECT * FROM LoanContact
	GO
	
	-- view loan contact by id
	CREATE PROC View_LoanContact_By_ID
		@id int 
	AS
		SELECT * FROM LoanContact WHERE LoanID = @id
	GO
	
	-- View loan by Amount
	CREATE PROC View_LoanContact_By_Amount
		@fromAmount MONEY,
		@toAmount MONEY
	AS
		SELECT * FROM LoanContact WHERE Amount BETWEEN @fromAmount AND @toAmount
	GO
	-- view loan by date
	CREATE PROC View_LoanContact_By_Date
		@fromDate DATETIME,
		@toDATE DATETIME
	AS
		SELECT * FROM LoanContact WHERE DateStart BETWEEN @fromDate AND @toDATE
	GO
	-- view Loan contact by Customer
	CREATE PROC View_LoanContact_By_CustomerID
		@customerID int
	AS
		SELECT * FROM LoanContact WHERE CustomerID = @customerID
	GO
	
	-- view Loan contact by Customer
	CREATE PROC View_LoanContact_By_Branch
		@userID INT
	AS
		SELECT * FROM LoanContact 
		WHERE UserID IN 
			(SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 
	GO
	-- 
	CREATE PROC View_LoanContact_By_ID_Branch
		@userID INT,
		@id INT
	AS
		SELECT * FROM LoanContact 
		WHERE LoanID = @id AND UserID IN 
			(SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 
	GO
	
	-- branch and amount
	CREATE PROC View_LoanContact_By_Amount_Branch
		@userID INT,
		@fromAmount MONEY,
		@toAmount MONEY
	AS
		SELECT * FROM LoanContact 
		WHERE  (Amount BETWEEN @fromAmount AND @toAmount) AND UserID IN 
			(SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 
	GO
	
	CREATE PROC View_LoanContact_By_Date_Branch
		@userID INT,
		@fromDate DATETIME,
		@toDate DATETIME
	AS
		SELECT * FROM LoanContact 
		WHERE  (DateStart BETWEEN @fromDate AND @toDate) AND UserID IN 
			(SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 
	GO
	--view by customer branch 
	CREATE PROC View_LoanContact_By_CustomerID_Branch
		@userid INT,
		@customerID INT
		
	AS
		SELECT * FROM LoanContact 
		WHERE  CustomerID = @customerID AND UserID IN 
			(SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 
	GO
	
	CREATE PROC View_LoanContact_All
		@select INT
	AS
		IF(@select = 1 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact	
			)
			AS l WHERE l.LoanTypeID = 1
		END
		
		IF(@select = 2 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact	
			)
			AS l WHERE l.LoanTypeID = 2
		END
		
		IF(@select = 3 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact	
			)
			AS l WHERE l.LoanTypeID = 3
		END
		
		IF(@select = 4 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact	
			)
			AS l WHERE l.LoanTypeID = 4
		END
		
		IF(@select = 5 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact	
			)
			AS l WHERE l.IsAccepted = 2
		END
		
		IF(@select = 6 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact	
			)
			AS l WHERE l.IsAccepted = 1
		END
		
		IF(@select = 7 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact	
			)
			AS l WHERE l.LoanTypeID = 0
		END
	GO
	
	CREATE PROC View_LoanContact_Branch
		@userID INT,
		@select INT
	AS
		IF(@select = 1 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact 
					WHERE UserID IN 
					(SELECT UserID FROM [User] 
						WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID))	
			)
			AS l WHERE l.LoanTypeID = 1
		END
		
		IF(@select = 2 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact 
					WHERE UserID IN 
					(SELECT UserID FROM [User] 
						WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID))	
			)
			AS l WHERE l.LoanTypeID = 2
		END
		
		IF(@select = 3 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact 
					WHERE UserID IN 
					(SELECT UserID FROM [User] 
						WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID))	
			)
			AS l WHERE l.LoanTypeID = 3
		END
		
		IF(@select = 4 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact 
					WHERE UserID IN 
					(SELECT UserID FROM [User] 
						WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID))	
			)
			AS l WHERE l.LoanTypeID = 4
		END
		
		IF(@select = 5 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact 
					WHERE UserID IN 
					(SELECT UserID FROM [User] 
						WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID))	
			)
			AS l WHERE l.IsAccepted = 2
		END
		
		IF(@select = 6 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact 
					WHERE UserID IN 
					(SELECT UserID FROM [User] 
						WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID))	
			)
			AS l WHERE l.IsAccepted = 1
		END
		
		IF(@select = 7 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact 
					WHERE UserID IN 
					(SELECT UserID FROM [User] 
						WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID))	
			)
			AS l WHERE l.LoanTypeID = 0
		END
	GO
	
	CREATE PROC Insert_LoanContact
		@CustomerID		INT,
		@LoanTypeID		INT,
		@UserID			INT,
		@Amount			MONEY,
		@Period			INT,
		@dateStart		DATETIME
	AS
		INSERT INTO LoanContact VALUES(@CustomerID,@LoanTypeID,@UserID,0,@Amount,@Period,@dateStart,null,2)
	GO
	-- calculate							
	CREATE PROC Cal_Payment
		@loanID INT,
		@loanTypeID INT
	AS
		DECLARE @months INT
		DECLARE @IntestRate FLOAT
		DECLARE @payment MONEY
		DECLARE @DateStart DATETIME
		DECLARE @Amount Money
		
		SELECT @IntestRate = (InterestRate/12) FROM LoanType WHERE LoanTypeID = @loanTypeID
		
		SELECT	@months = Period ,
				@payment = (Amount/@months),
				@DateStart = DateStart,
				@Amount = Amount
		FROM LoanContact 
		WHERE LoanID = @loanID
		
		DECLARE @i INT
		SET @i = 0
		
		WHILE @i < @months
		BEGIN
			SET @i = @i + 1
			DECLARE @Interest MONEY
			SET @Interest = (@Amount - (@i-1)*@payment)*@IntestRate*0.01
			
			INSERT INTO Payment Values 
			(
				
				@loanID,@i,@payment,@Interest,0,
				@payment+@Interest,
				DATEADD(MONTH,@i,@DateStart),null,
				0
			)
		END
		
		GO
		
		--payment
		CREATE PROC Update_Payment
			@paymentID INT
		AS
			UPDATE Payment SET Status = 1 , DatePaid = GETDATE() WHERE PaymentID = @paymentID
		GO
		
		--view and fine
		CREATE PROC View_Payment
			@loanID INT
		AS
			SELECT * FROM Payment WHERE LoanID  = @loanID
		GO
		-- cal Fine
		CREATE PROC Cal_Fine
			@loanID INT
		AS
			DECLARE @Months INT
			SELECT @Months = Period FROM LoanContact WHERE LoanID  = @loanID
			DECLARE @i INT
			SET @i = 0
			
			WHILE @i <= @months
			BEGIN 
				SET @i = @i+1
				DECLARE @DureDate DATETIME
				DECLARE @Status BIT
				DECLARE @payment MONEY
				DECLARE @Interest MONEY
				DECLARE @fineAmount MONEY
				
				SELECT @DureDate = DureDate, @Status = [Status],@payment = Payment, @Interest = Interest , @fineAmount = FineAmount 
				FROM Payment
				WHERE LoanID = @loanID AND [Month] = @i
				
				
				
				IF((GETDATE()> @DureDate) AND @Status = 0 AND @fineAmount = 0)
				BEGIN
					DECLARE @count INT
					SELECT @count = COUNT(*) FROM FineLoan
					DECLARE @fine FLOAT
					DECLARE @j INT
					SET @j = 0
					
				    WHILE @j<@count
				    BEGIN
						SET @j = @j+1
						DECLARE @from MONEY
						DECLARE @to MONEY
						DECLARE @fines FLOAT
						SELECT @from = FromMoney , @to = ToMoney , @fines = Fine FROM FineLoan 
						WHERE FineID = @j		
						IF @to = 0
						BEGIN
							IF @payment >= @from
							BEGIN 
								SET @fine = @fines
							END
						END
						
						ELSE
						BEGIN
							IF( @payment >= @from AND @payment <= @to)
							BEGIN 
								SET @fine = @fines
							END
						END
						
				    END
					UPDATE Payment SET FineAmount = (@payment*@fine*0.01) , TotalPay = (@payment + @payment*@fine*0.01+@Interest)
					WHERE LoanID  = @loanID AND [Month] = @i
				END
			END
			
			
	GO
		
	CREATE PROC Accepted_LoanContact
		@loanID INT,
		@userID INT
	AS
		UPDATE LoanContact 
		SET IsAccepted = 1 , UserAccecpted = @userID ,DateApproved = GETDATE()
		WHERE LoanID = @loanID 
	GO
	
	CREATE PROC NotAccepted_LoanContact
		@loanID INT,
		@userID INT
	AS
		UPDATE LoanContact 
		SET IsAccepted = 0 , UserAccecpted = @userID ,DateApproved = GETDATE()
		WHERE LoanID = @loanID 
	GO
	
	CREATE PROC Cal_TotalAmount
		
	AS
		SELECT SUM(Amount) AS TotalAmount FROM LoanContact
	GO
	
	CREATE PROC Cal_TotalFine
	AS
		SELECT SUM(FineAmount) AS [TotalFine] FROM Payment
	GO
	
	CREATE PROC Cal_Interest
	AS
		SELECT SUM(Interest) AS [TotalInterest] FROM Payment
	GO
	
	CREATE PROC Cal_TotalPayment
	AS
		SELECT SUM(TotalPay) AS [TotalPayment] FROM Payment
	GO
	
	CREATE PROC Cal_TotalLoan
	AS
		SELECT Count(*) AS [TotalLoan] FROM LoanContact
	GO
	
	
/*=================Customer==============*/
	-- view all customer
	CREATE PROC View_all_Customer
	AS
		SELECT * FROM Customer
	GO
	
	-- view Customer by id
	CREATE PROC View_Customer_By_ID
		@id int 
	AS
		SELECT * FROM Customer WHERE CustomerID = @id
	GO
	
	
	-- view customer  in branch 
	CREATE PROC View_Customer_By_Branch
		@userID INT
	AS
		SELECT * FROM Customer 
		WHERE UserID IN 
			(SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 
	GO

	
	-- view customer in branch 
	CREATE PROC View_Customer_By_ID_Branch
		@customerID INT,		
		@userID INT
	AS
		SELECT * FROM Customer 
		WHERE CustomerID =  @customerID AND UserID IN 
			(SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 
	GO
	
	--view customer by name 
	CREATE PROC View_Customer_By_Name_Branch
		@userID INT,
		@customerName VARCHAR(100) 
	AS
		SELECT * FROM Customer 
		WHERE  (FirstName LIKE ('%' + @customerName + '%')  OR LastName LIKE ('%' + @customerName + '%'))  AND 
			UserID IN (SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 
		GO  
		
	--search customer by ID
CREATE PROC View_Customer_By_Name
		@customerName VARCHAR(100) 
	AS
		SELECT * FROM Customer 
		WHERE  (FirstName LIKE ('%' + @customerName + '%')  OR FirstName LIKE ('%' + @customerName + '%'))
	GO

-- Search customer by organzination
CREATE PROC View_Customer_By_Organzination
@org varchar(100)
AS
SELECT * FROM Customer
WHERE Organzination like ('%' + @org + '%')

GO

CREATE PROC View_Customer_By_Organzination_Branch
@org varchar(100),
@userID int
AS
SELECT * FROM Customer
WHERE Organzination like ('%' + @org + '%') AND
 UserID IN (SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 



GO
--Search customer bu identity
CREATE PROC View_Customer_By_identity
@identity varchar(100)
AS
SELECT * FROM Customer
WHERE @identity = @identity

GO

CREATE PROC View_Customer_By_identity_Branch
@identity varchar(100),
@userID int
AS
SELECT * FROM Customer
WHERE @identity = @identity AND
UserID IN (SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 

GO

--Search customer bu Gross
CREATE PROC View_Customer_By_Gross
@gross float
AS
SELECT * FROM Customer
WHERE GrossSalary = @gross

GO

CREATE PROC View_Customer_By_Gross_Branch
@gross float,
@userID int
AS
SELECT * FROM Customer
WHERE GrossSalary = @gross AND
UserID IN (SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 

GO

	-- insert Customer 
	CREATE PROC Insert_Customer
		@UserID			INT,
		@firstName		VARCHAR(50),
		@LastName		VARCHAR(50),
		@address		VARCHAR(100),
		@birthday		SMALLDATETIME,
		@PhoneNumber	VARCHAR(15),
		@Email			VARCHAR(100),
		@Identity		CHAR(9),
		@Sex			BIT,
		@Organzination	VARCHAR(100),
		@GrossSalary	MONEY,
		@Note			VARCHAR(500),
		@Status			BIT 
		
	AS
		INSERT INTO Customer VALUES (	@UserID,@firstName,@LastName , @address,
										@birthday,@PhoneNumber,@Email,@Identity,
										@Sex,@Organzination,@GrossSalary,@Note,@Status
									)
	GO
	
	-- update customer
	CREATE PROC Update_Customer
		@CustomerID		INT,
		@UserID			INT,
		@firstName		VARCHAR(50),
		@LastName		VARCHAR(50),
		@address		VARCHAR(100),
		@birthday		SMALLDATETIME,
		@PhoneNumber	VARCHAR(15),
		@Email			VARCHAR(100),
		@Identity		CHAR(9),
		@Sex			BIT,
		@Organzination	VARCHAR(100),
		@GrossSalary	MONEY,
		@Note			VARCHAR(500)		
	AS
		UPDATE Customer 
							SET UserID	= @UserID,
							FirstName	= @firstName,
							LastName	= @LastName , 
							[Address]	= @address,
							BrithDay	= @birthday,
							PhoneNumber	= @PhoneNumber,
							Email		= @Email,
							[Identity] = @Identity,
							Sex			= @Sex,
							Organzination = @Organzination,
							GrossSalary = @GrossSalary,
							Note		= @Note
		WHERE CustomerID = @CustomerID				
	GO
			
	-- Disable Customer
	CREATE PROC Disable_Customer
		@CustomerID INT
	AS
		UPDATE Customer SET [Status] = 1 
		WHERE CustomerID = @CustomerID
	GO
	
	CREATE PROC Enable_Customer
		@CustomerID INT
	AS
		UPDATE Customer SET [Status] = 0 
		WHERE CustomerID = @CustomerID
	GO
	CREATE PROC cal_totalCustomer
		
	AS
		SELECT COUNT(*) AS [TotalCustomer] FROM Customer
	GO					





